package com.group6.exam.teacher.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.group6.exam.common.entity.ExamEntity;
import com.group6.exam.common.entity.PageModel;
import com.group6.exam.common.util.DBUtil;

public class ExamDaoImpl implements IExamDao {

	/**
	 * 查询科目
	 */
	public List<Map<String, Object>> getSubject() {
		String sql = "select * from subject";
		return DBUtil.executeQuery(sql);
	}

	/**
	 * 查询发布试卷用户
	 */
	public List<Map<String, Object>> getUserInfo() {
		String sql = "select * from userInfo";
		return DBUtil.executeQuery(sql);
	}

	/**
	 * 查询试卷
	 */
	public List<Map<String, Object>> getPaper() {
		String sql = "select * from paper";
		return DBUtil.executeQuery(sql);
	}

	/**
	 * 获取总记录数
	 */
	public int getTotalRecords(String gradeName) {
		
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from examarrange");
		if (gradeName != null && !"".equals(gradeName)) {
			gradeName = "%" + gradeName + "%";
			String sql = "select * from grade where grade_name like ?";
			Map<String, Object> gradeId = DBUtil.executeQuery(sql, gradeName).get(0);
			sb.append(" where exam_grade_id=?");
			paramList.add(gradeId.get("grade_id"));
		}
		Object[] parameters = paramList.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters).size();
	}

	/**
	 * 获取有考试信息
	 */
	public List<Map<String, Object>> getExamPage(PageModel pageModel, String gradeName) {
		
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		sb.append("select * from examarrange");
		
		// 条件查询
		if (gradeName != null && !"".equals(gradeName)) {
			gradeName = "%" + gradeName + "%";
			String sql = "select * from grade where grade_name like ?";
			Map<String, Object> gradeId = DBUtil.executeQuery(sql, gradeName).get(0);
			sb.append(" where exam_grade_id=?");
			paramList.add(gradeId.get("grade_id"));
		}
		sb.append(" order by exam_id desc limit ?, ?");
		
		int index = (pageModel.getCurrentPage()-1)*pageModel.getPageSize();
		int pageSize = pageModel.getPageSize();
		paramList.add(index);
		paramList.add(pageSize);
		Object[] parameters = paramList.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters);
	}

	/**
	 * 删除考试信息
	 */
	public int delete(String examId) {
		String sql = "delete from examarrange where exam_id=?";
		return DBUtil.executeUpdate(sql, examId);
	}

	/**
	 * 修改前查询
	 */
	public Map<String, Object> preUpdate(String examId) {
		String sql = "select * from examarrange where exam_id=?";
		return DBUtil.executeQuery(sql, examId).get(0);
	}

	/**
	 * 添加考试信息
	 */
	public int add(ExamEntity exam) {
		String sql = "insert into examarrange values(null,?,?,?,?,now(),?)";
		Object[] parameters = {
				exam.getGradeId(),
				exam.getExamTime(),
				exam.getSubjectId(),
				exam.getPaperId(),
				exam.getUserId()
		};
		return DBUtil.executeUpdate(sql, parameters);
	}

	/**
	 * 修改考试信息
	 */
	public int update(ExamEntity exam) {
		String sql = "update examarrange set exam_grade_id=?, " 
				+ "exam_examtime=?, exam_subject_id=?, exam_paper_id=?, "
				+ "exam_user_id=? where exam_id=?";
		Object[] parameters = {
			exam.getGradeId(),
			exam.getExamTime(),
			exam.getSubjectId(),
			exam.getPaperId(),
			exam.getUserId(),
			exam.getId()
		};
		return DBUtil.executeUpdate(sql, parameters);
	}

	/**
	 * 获取所有未参考学生总记录
	 */
	public int notExamTotalRecords(String gradeName) {
		
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		
		sb.append("SELECT stu.student_name AS 'studentname', ");
		sb.append("g.grade_name AS 'gradename', s.subject_name AS 'subjectname', ");
		sb.append("stu.student_sno AS 'studentnum' FROM `subject` s, studentinfo stu, ");
		sb.append("score sc, grade g WHERE sc.score_grade_id=g.grade_id AND ");
		sb.append("sc.score_student_id=stu.student_id ");
		sb.append("AND sc.score_subject_id=s.subject_id AND sc.score_score=0");
		
		// 条件查询
		if (gradeName != null && !"".equals(gradeName)) {
			gradeName = "%" + gradeName + "%";
			String sql = "select * from grade where grade_name like ?";
			Map<String, Object> gradeId = DBUtil.executeQuery(sql, gradeName).get(0);
			sb.append(" AND sc.score_grade_id=?");
			paramList.add(gradeId.get("grade_id"));
		}
		
		Object[] parameters = paramList.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters).size();
	}

	
	/**
	 * 获取所有未参考学生的集合
	 */
	public List<Map<String, Object>> notExamList(PageModel pageModel, String gradeName) {
		
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		
		sb.append("SELECT stu.student_name AS 'studentname', ");
		sb.append("g.grade_name AS 'gradename', s.subject_name AS 'subjectname', ");
		sb.append("stu.student_sno AS 'studentnum' FROM `subject` s, studentinfo stu, ");
		sb.append("score sc, grade g WHERE sc.score_grade_id=g.grade_id AND ");
		sb.append("sc.score_student_id=stu.student_id ");
		sb.append("AND sc.score_subject_id=s.subject_id AND sc.score_score=0");
		
		// 条件查询
		if (gradeName != null && !"".equals(gradeName)) {
			gradeName = "%" + gradeName + "%";
			String sql = "select * from grade where grade_name like ?";
			Map<String, Object> gradeId = DBUtil.executeQuery(sql, gradeName).get(0);
			sb.append(" AND sc.score_grade_id=?");
			paramList.add(gradeId.get("grade_id"));
		}
		sb.append(" limit ?,?");
		
		int index = (pageModel.getCurrentPage()-1)*pageModel.getPageSize();
		int pageSize = pageModel.getPageSize();
		paramList.add(index);
		paramList.add(pageSize);
		Object[] parameters = paramList.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters);
	}

	/**
	 * 考试记录明细分页查询
	 */
	public List<Map<String, Object>> examInfo(PageModel pageModel,
			String gradeName) {
		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		
		sb.append("SELECT g.grade_name AS 'gradeName', ");
		sb.append("s.subject_name AS 'subjectName', e.exam_examtime AS 'examTime', ");
		sb.append("p.paper_name AS 'paperName' FROM grade g, `subject` s, ");
		sb.append("examarrange e, paper p WHERE g.grade_id=e.exam_grade_id ");
		sb.append("AND s.subject_id=e.exam_subject_id AND p.paper_id=e.exam_paper_id");
		
		// 条件查询
		if (gradeName != null && !"".equals(gradeName)) {
			gradeName = "%" + gradeName + "%";
			String sql = "select * from grade where grade_name like ?";
			Map<String, Object> gradeId = DBUtil.executeQuery(sql, gradeName).get(0);
			sb.append(" AND e.exam_grade_id=?");
			paramList.add(gradeId.get("grade_id"));
		}
		sb.append(" limit ?,?");
		
		int index = (pageModel.getCurrentPage()-1)*pageModel.getPageSize();
		int pageSize = pageModel.getPageSize();
		paramList.add(index);
		paramList.add(pageSize);
		Object[] parameters = paramList.toArray();
		return DBUtil.executeQuery(sb.toString(), parameters);
	}

}
